/*
 Navicat Premium Data Transfer

 Source Server         : 边边
 Source Server Type    : MySQL
 Source Server Version : 50721
 Source Host           : localhost
 Source Database       : si_db

 Target Server Type    : MySQL
 Target Server Version : 50721
 File Encoding         : utf-8

 Date: 04/03/2018 10:13:44 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `building`
-- ----------------------------
DROP TABLE IF EXISTS `building`;
CREATE TABLE `building` (
  `community_id` int(11) NOT NULL AUTO_INCREMENT,
  `community_name` varchar(255) DEFAULT NULL,
  `community_create_date` datetime DEFAULT NULL,
  `community_update_date` datetime DEFAULT NULL,
  `build_company` varchar(255) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`community_id`),
  KEY `project_id` (`project_id`),
  CONSTRAINT `building_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `device`
-- ----------------------------
DROP TABLE IF EXISTS `device`;
CREATE TABLE `device` (
  `deviceid` int(11) NOT NULL AUTO_INCREMENT,
  `mode` varchar(255) DEFAULT NULL,
  `gatewayid` int(11) DEFAULT NULL,
  PRIMARY KEY (`deviceid`),
  KEY `gatewayid` (`gatewayid`),
  CONSTRAINT `device_ibfk_1` FOREIGN KEY (`gatewayid`) REFERENCES `gateway` (`gatewayid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `deviceaction`
-- ----------------------------
DROP TABLE IF EXISTS `deviceaction`;
CREATE TABLE `deviceaction` (
  `deviceid` int(11) DEFAULT NULL,
  `params` varchar(255) DEFAULT NULL,
  `actionname` varchar(255) DEFAULT NULL,
  KEY `deviceid` (`deviceid`),
  CONSTRAINT `deviceaction_ibfk_1` FOREIGN KEY (`deviceid`) REFERENCES `device` (`deviceid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `gateway`
-- ----------------------------
DROP TABLE IF EXISTS `gateway`;
CREATE TABLE `gateway` (
  `gatewayid` int(11) NOT NULL AUTO_INCREMENT,
  `houseId` int(11) DEFAULT NULL,
  `gatewayName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`gatewayid`),
  KEY `houseId` (`houseId`),
  CONSTRAINT `gateway_ibfk_1` FOREIGN KEY (`houseId`) REFERENCES `house` (`house_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `house`
-- ----------------------------
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
  `house_id` int(11) NOT NULL AUTO_INCREMENT,
  `house_name` varchar(255) DEFAULT NULL,
  `house_create_date` datetime DEFAULT NULL,
  `house_update_date` datetime DEFAULT NULL,
  `house_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`house_id`),
  KEY `house_type_id` (`house_type_id`),
  CONSTRAINT `house_ibfk_1` FOREIGN KEY (`house_type_id`) REFERENCES `housetype` (`housetype_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `housetype`
-- ----------------------------
DROP TABLE IF EXISTS `housetype`;
CREATE TABLE `housetype` (
  `housetype_id` int(11) NOT NULL,
  `housetype_name` varchar(255) DEFAULT NULL,
  `community_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`housetype_id`),
  KEY `community_id` (`community_id`),
  CONSTRAINT `housetype_ibfk_1` FOREIGN KEY (`community_id`) REFERENCES `building` (`community_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `module_define`
-- ----------------------------
DROP TABLE IF EXISTS `module_define`;
CREATE TABLE `module_define` (
  `module_id` int(4) NOT NULL,
  `module_name` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '模块名如 项目管理',
  `module_parent` int(4) DEFAULT NULL COMMENT '上级模块',
  `module_hierarchy` varchar(512) CHARACTER SET utf8 DEFAULT NULL COMMENT '层级',
  `module_level` int(2) DEFAULT NULL COMMENT '级别',
  `module_url` varchar(512) CHARACTER SET utf8 DEFAULT NULL COMMENT '链接',
  `icon_name` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '图标名',
  PRIMARY KEY (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `permission_define`
-- ----------------------------
DROP TABLE IF EXISTS `permission_define`;
CREATE TABLE `permission_define` (
  `role_id` int(4) DEFAULT NULL COMMENT '角色编号',
  `module_id` int(4) DEFAULT NULL COMMENT '模块编号',
  `crud_operation` int(2) DEFAULT NULL COMMENT '操作权限如 15 对应二进制1111 为增删改查',
  KEY `模块编号` (`module_id`),
  KEY `授权角色id` (`role_id`),
  CONSTRAINT `授权模块id` FOREIGN KEY (`module_id`) REFERENCES `module_define` (`module_id`) ON DELETE SET NULL,
  CONSTRAINT `授权角色id` FOREIGN KEY (`role_id`) REFERENCES `role_define` (`role_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `project`
-- ----------------------------
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_name` varchar(255) DEFAULT NULL,
  `pro_create_date` datetime DEFAULT NULL,
  `pro_update_date` datetime DEFAULT NULL,
  `create_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `role_define`
-- ----------------------------
DROP TABLE IF EXISTS `role_define`;
CREATE TABLE `role_define` (
  `role_id` int(4) NOT NULL,
  `role_name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '角色名',
  `role_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '角色描述',
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `role_define`
-- ----------------------------
BEGIN;
INSERT INTO `role_define` VALUES ('0', 'admin', '管理者');
COMMIT;

-- ----------------------------
--  Table structure for `room`
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
  `roomid` int(11) NOT NULL,
  `roomname` varchar(255) DEFAULT NULL,
  `housetypeid` int(11) DEFAULT NULL,
  PRIMARY KEY (`roomid`),
  KEY `houseid` (`housetypeid`),
  CONSTRAINT `room_ibfk_1` FOREIGN KEY (`housetypeid`) REFERENCES `housetype` (`housetype_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `scenario`
-- ----------------------------
DROP TABLE IF EXISTS `scenario`;
CREATE TABLE `scenario` (
  `scenarioid` int(11) NOT NULL,
  `deviceActions` bit(1) DEFAULT NULL,
  PRIMARY KEY (`scenarioid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `t_device_room`
-- ----------------------------
DROP TABLE IF EXISTS `t_device_room`;
CREATE TABLE `t_device_room` (
  `deviceid` int(11) DEFAULT NULL,
  `roomid` int(11) DEFAULT NULL,
  KEY `deviceid` (`deviceid`),
  KEY `roomid` (`roomid`),
  CONSTRAINT `t_device_room_ibfk_1` FOREIGN KEY (`deviceid`) REFERENCES `device` (`deviceid`) ON DELETE SET NULL,
  CONSTRAINT `t_device_room_ibfk_2` FOREIGN KEY (`roomid`) REFERENCES `room` (`roomid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `t_house_device`
-- ----------------------------
DROP TABLE IF EXISTS `t_house_device`;
CREATE TABLE `t_house_device` (
  `houseid` int(11) DEFAULT NULL,
  `deviceid` int(11) DEFAULT NULL,
  KEY `houseid` (`houseid`),
  KEY `deviceid` (`deviceid`),
  CONSTRAINT `t_house_device_ibfk_1` FOREIGN KEY (`houseid`) REFERENCES `house` (`house_id`) ON DELETE SET NULL,
  CONSTRAINT `t_house_device_ibfk_2` FOREIGN KEY (`deviceid`) REFERENCES `device` (`deviceid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `t_house_scenarioid`
-- ----------------------------
DROP TABLE IF EXISTS `t_house_scenarioid`;
CREATE TABLE `t_house_scenarioid` (
  `houseid` int(11) DEFAULT NULL,
  `scenarioid` int(11) DEFAULT NULL,
  KEY `houseid` (`houseid`),
  KEY `scenarioid` (`scenarioid`),
  CONSTRAINT `t_house_scenarioid_ibfk_1` FOREIGN KEY (`houseid`) REFERENCES `house` (`house_id`) ON DELETE SET NULL,
  CONSTRAINT `t_house_scenarioid_ibfk_2` FOREIGN KEY (`scenarioid`) REFERENCES `scenario` (`scenarioid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `t_scenario_room`
-- ----------------------------
DROP TABLE IF EXISTS `t_scenario_room`;
CREATE TABLE `t_scenario_room` (
  `scenarioid` int(11) DEFAULT NULL,
  `roomid` int(11) DEFAULT NULL,
  KEY `roomid` (`roomid`),
  KEY `scenarioid` (`scenarioid`),
  CONSTRAINT `t_scenario_room_ibfk_1` FOREIGN KEY (`roomid`) REFERENCES `room` (`roomid`) ON DELETE SET NULL,
  CONSTRAINT `t_scenario_room_ibfk_2` FOREIGN KEY (`scenarioid`) REFERENCES `scenario` (`scenarioid`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `t_user_house`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_house`;
CREATE TABLE `t_user_house` (
  `user_id` int(11) DEFAULT NULL,
  `house_id` int(11) DEFAULT NULL,
  `crud_operation` int(2) DEFAULT NULL,
  KEY `house_id` (`house_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `t_user_house_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `house` (`house_id`) ON DELETE SET NULL,
  CONSTRAINT `t_user_house_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `t_user_project`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_project`;
CREATE TABLE `t_user_project` (
  `use_id` int(4) DEFAULT NULL,
  `project_id` int(4) DEFAULT NULL,
  `crud_operation` int(2) DEFAULT NULL,
  KEY `use_id` (`use_id`),
  KEY `project_id` (`project_id`),
  CONSTRAINT `t_user_project_ibfk_1` FOREIGN KEY (`use_id`) REFERENCES `user` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `t_user_project_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户名',
  `hash_password` varchar(1024) CHARACTER SET utf8 DEFAULT NULL COMMENT '加密后的密码',
  `salt` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '加密的盐',
  `accessToken` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT 'accessToken',
  `avatar` varchar(500) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户头像',
  `role_id` int(4) DEFAULT NULL COMMENT '角色id',
  `phone` varchar(11) CHARACTER SET utf8 DEFAULT NULL COMMENT '手机号',
  `email` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户邮箱',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  KEY `角色编号` (`role_id`),
  CONSTRAINT `用户角色id` FOREIGN KEY (`role_id`) REFERENCES `role_define` (`role_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `user`
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES ('3', 'admin', 'L49lzLBIcdwRoZGXmBfQTNldQPKwdsGSswc/w3YqQ+TjtYJ4jRtbkjqfIKdZqCd3SjlW2SWPhZH+JLpwTrA5WQ==', 'NXLJHy59ZrhV45F+oyh1TA==', null, null, '0', null, null, '2018-04-02 15:10:28');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
